******************************************************************************************************************************************* fragment
**************************************** MERGING THE DATABASES
******************************************************************************************************************************************* fragment
clear all 
set mem 3g
set more off
set matsize 11000
cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
********** create a database with the enrollees which firms we use for the demand estimation
use enrollment_collapsed_january.dta, clear
sort polnbr date_month
merge polnbr date_month using firm_dictionary.dta
drop if _merge!=3
*drop if num_plansbyfirm>1
drop _merge
tempfile enrollment 
save `enrollment'
************* create a database at the member level 
use plan_characteristics.dta, clear
gen outvalue = 0 
collapse (mean) outvalue, by(customer_number minor_market sic_cd date_month)
drop outvalue
gen polnbr="0"
local var "par2 par in_network_deductible total_premium in_network_oopm coinsurance phys_copay_dif0 phys_copay_intensity outnet_oop_dif0 outnet_oop_intensity outnet_ded_intensity avg_price_a avg_price_b avg_price_c avg_price_d NestedLogitIV_11 NestedLogitIV_12 NestedLogitIV_13 NestedLogitIV_14 NestedLogitIV_2 NestedLogitIV_3 NestedLogitIV_4 NestedLogitIV_5 NestedLogitIV_6 NestedLogitIV_7 NestedLogitIV_8 NestedLogitIV_9 NestedLogitIV_10"
foreach x of local var{
gen `x' = 0 
}
forvalues i = 1/615 {
gen rx_dum`i'=0
}
append using plan_characteristics.dta
sort  customer_number date_month 
* create the database with the choice set for each member 
joinby customer_number date_month using `enrollment'
gen choice =( polnbr ==prime_policy )
order indv_id mbr_sys_id alt_id rel_cd age gdr_cd customer_number minor_market date_month polnbr choice prime_policy
sort  indv_id mbr_sys_id age gdr_cd customer_number minor_market date_month polnbr choice prime_policy
drop prime_policy cust_seg_nbr min_initial max_finish month 
gen year = year(dofm(date_month))
sort year mbr_sys_id polnbr
*
merge year mbr_sys_id polnbr using total_claims.dta 
replace net_pd_amt=0 if _merge==1 
replace allw_amt=0 if _merge==1 
replace oop_dircomputed=0 if _merge==1
local var "code_asthma code_atrialfibrilization code_chronickidneydisease code_heartfailure code_hypertension code_cancer_b code_cancer code_cancer2 code_traubraininj code_transplant code_leukemia code_septicemia code_coronaryatheros code_heartvalve code_brainhemorr code_respfailure code_sepshock code_respfainewborn code_respdistrnewborn code_kidneyfail code_respfaisurg code_neumonia code_brainedema code_pancytopenia code_posthemorranemia mem_died code_ami code_diabetes"
foreach x of local var {
replace `x'=0 if _merge==1
}
*
drop if _merge==2
drop _merge 
sort year mbr_sys_id polnbr
merge year mbr_sys_id polnbr using total_pharmaclaims.dta 
*merge year mbr_sys_id polnbr using total_drugclaims.dta 
rename pd_amt paid_pharmacy
gen allowed_pharma = copay_amt + ded_amt + paid_pharmacy
*rename sbmt_chrg_amt allowed_pharma
replace paid_pharmacy=0 if _merge==1
replace allowed_pharma=0 if _merge==1
drop if _merge==2
drop _merge 
***

cd "C:\Users\sfleit2\Documents\ReclassificationRisk_DoFiles"
save database_by_members_n_choiceset.dta, replace
